package Dao.dao;

import Chapter01.H20211215.DBUtil;
import Chapter01.H20211215.Subject;
import Dao.entity.Result;
import Dao.entity.Student;

import java.io.Serializable;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class ExamresultDao {
    //增加
    public int add(Result r){
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");
            String sql="insert into `s_examresult`(`exam_date`,`score`,`st_id`,`sb_id`)values(?,?,?,?)";
            System.out.println(sql);
            ps=connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setObject(1,r.getExam_date());
            ps.setDouble(2,r.getScore());
            ps.setInt(3,r.getSt_id());
            ps.setInt(4,r.getSb_id());
            zhi=ps.executeUpdate();
            System.out.printf("%d行被修改了",zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(connection);
        }
        return zhi;
    }
    //查
    public List<Result> all(){
        List<Result> list=new LinkedList<>();
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=connection.prepareStatement("select * from s_examresult");
            rs=ps.executeQuery();
            while (rs.next()){
                Result result=new Result();
                result.setId(rs.getLong("id"));
                result.setExam_date(rs.getDate("exam_date"));
                result.setScore(rs.getDouble("score"));
                result.setSt_id(rs.getInt("st_id"));
                result.setSb_id(rs.getInt("sb_id"));
                list.add(result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return list;
    }

    //删除
    public int del( int id){
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");
            String sql="delete from s_examresult where id=?";
            System.out.println(sql);
            ps=connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setInt(1,id);
            zhi=ps.executeUpdate();
            System.out.printf("%d行被修改了",zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(connection);
        }
        return zhi;
    }

    //修改
    public int update(Result r) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");

            String sql = "update s_examresult set exam_date=?,score=?,st_id=?,sb_id=?where id=?";
            System.out.println(sql);
            ps = connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setObject(1,r.getExam_date());
            ps.setDouble(2, r.getScore());
            ps.setInt(3, r.getSt_id());
            ps.setInt(4, r.getSb_id());
            ps.setLong(5,r.getId());
            zhi = ps.executeUpdate();
            System.out.printf("%d行被修改了", zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Chapter01.DBUtil.close(ps);
            Chapter01.DBUtil.close(connection);
        }
        return zhi;
    }
}
